package com.worthtech.billportal.dao.hibernate;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.worthtech.billportal.bean.CsAppTerminal;
import com.worthtech.billportal.bean.CsBakTerminal;
import com.worthtech.billportal.bean.CsBillId;
import com.worthtech.billportal.bean.CsBillMsg;
import com.worthtech.billportal.bean.CsBillShop;
import com.worthtech.billportal.bean.CsCtrlUser;
import com.worthtech.billportal.bean.CsDicCommbank;
import com.worthtech.billportal.bean.CsLiqPara;
import com.worthtech.billportal.bean.CsMgrCode;
import com.worthtech.billportal.bean.CsRcvAccountInfo;
import com.worthtech.billportal.bean.CsSysTerminal;
import com.worthtech.billportal.bean.MenuDefine;
import com.worthtech.billportal.bean.PepCustinfo;
import com.worthtech.billportal.bean.SamcardInfo;
import com.worthtech.billportal.bean.WlId;
import com.worthtech.billportal.bean.WtMgrEncKey;
import com.worthtech.billportal.bean.WtMgrMerInfo;
import com.worthtech.billportal.bean.WtRcvAccountInfo;
import com.worthtech.billportal.bean.WtTermInfo;
import com.worthtech.billportal.dao.SaminfoDAO;
import com.worthtech.billportal.util.AbstractManager;
import com.worthtech.billportal.util.Util;

public class SaminfoHibernateDAO extends AbstractManager implements SaminfoDAO {

	public List getListByHQL(String tableName, int startPage, int perNo,
			String orderby) {
		String hql = "from " + tableName + " " + orderby;
		System.out.println("hql2=" + hql);
		if(perNo==0){
			List l = this.getHibernateTemplate().find(hql);
			System.out.println("size = "+l.size());
			return l;
		}
		return this.getSession().createQuery(hql).setFirstResult(startPage)
				.setMaxResults(perNo).list();
	}
	public void delete(CsAppTerminal c){
		this.getHibernateTemplate().delete(c);
	}
	public void save(PepCustinfo p){
		this.getHibernateTemplate().save(p);
	}
	public void save(WtTermInfo w){
		this.getHibernateTemplate().save(w);
	}
	public void save(CsDicCommbank c){
		this.getHibernateTemplate().save(c);
	}
	public void delete(CsDicCommbank c){
		this.getHibernateTemplate().delete(c);
	}
	public void save(CsLiqPara c){
		this.getHibernateTemplate().save(c);
	}
	public void save(CsBillShop c){
		this.getHibernateTemplate().save(c);
	}
	public void delete(CsBillShop c){
		this.getHibernateTemplate().delete(c);
	}
	public void update(CsLiqPara c){
		this.getHibernateTemplate().update(c);
	}
	public void delete(CsLiqPara c){
		this.getHibernateTemplate().delete(c);
	}
	public void delete(CsMgrCode c){
		this.getHibernateTemplate().delete(c);
	}
	public void update(CsBillMsg c){
		this.getHibernateTemplate().update(c);
	}
	public void delete(CsBillMsg c){
		this.getHibernateTemplate().delete(c);
	}
	public void save(CsBillMsg c){
		this.getHibernateTemplate().save(c);
	}
	public void save (WtMgrEncKey w ){
		Connection con;
		PreparedStatement ps;
		String sql = "insert into wt_mgr_enc_key (MGR_INS_ID_CD, MGR_SYS_INDEX, MGR_TERM_ID, MGR_MER_ID, MGR_ENC_KEY_TP, MGR_ENC_KEY_INDEX, MGR_ENC_KEY_VAL, MGR_MN_KEY_INDEX, MGR_MN_KEY, MGR_KEY_MN_DES) values ('"+w.getId().getMgrInsIdCd()+"',"+w.getId().getMgrSysIndex()+", '"+w.getId().getMgrTermId()+"', '"+w.getId().getMgrMerId()+"', '"+w.getId().getMgrEncKeyTp()+"', '"+w.getMgrEncKeyIndex()+"', '"+w.getMgrEncKeyVal()+"', '"+w.getMgrMnKeyIndex()+"', '"+w.getMgrMnKey()+"', '"+w.getMgrKeyMnDes()+"')";
		con = this.getSession().connection();
		try {
			ps = con.prepareStatement(sql);
			int i= ps.executeUpdate();
//			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
//		System.out.println("wwwww="+w.getId().getMgrInsIdCd());
//		this.getHibernateTemplate().save(w);
	}
	
	public void saveJnls(String bit15,String mercode,String user) throws Exception{
		Connection con;
		PreparedStatement ps;
		String sql ="";
		if(mercode != null)
		sql= "insert into cs_ly_out (bit_15,mercode,tercode,trace,outcd,incd,tranamt,termid,aprespn,sysno,flag,apuser) " +
					 " (select trim(BIT_15),trim(FST_MERCODE),trim(FST_TERMCDE),trim(TRACE),trim(OUTCDNO),trim(INTCDNO)," +
					 "trim(TRANAMT),trim(TERMID),trim(FST_SDRESPN),trim(SYSEQNO),'00','"+user+"'" +
					 		" from wt_pep_jnls where BIT_15 = '"+bit15+"' and TRANCDE = '' )";
		else
			sql= "insert into cs_ly_out (bit_15,mercode,tercode,trace,outcd,incd,tranamt,termid,aprespn,sysno,flag,apuser) " +
			 " (select trim(BIT_15),trim(FST_MERCODE),trim(FST_TERMCDE),trim(TRACE),trim(OUTCDNO),trim(INTCDNO)," +
			 "trim(TRANAMT),trim(TERMID),trim(FST_SDRESPN),trim(SYSEQNO),'00','"+user+"'" +
			 		" from wt_pep_jnls where BIT_15 = '"+bit15+"' and TRANCDE = '' and FST_MERCODE = '"+mercode+"')";
		con = this.getSession().connection();
		ps = con.prepareStatement(sql);
		ps.executeUpdate();
	}
	public void save(WtMgrMerInfo w){
		this.getHibernateTemplate().save(w);
	}
	public void update(WtMgrEncKey w){
		this.getHibernateTemplate().update(w);
	}
	public void update(WtMgrMerInfo w){
		Connection con;
		PreparedStatement ps;
		String sql = "update WT_MGR_MER_INFO set MGR_FLAG = '4' where MGR_TERM_ID = '"+w.getId().getMgrTermId()+"' and MGR_MER_ID = '"+w.getId().getMgrMerId().trim()+"' ";
		con = this.getSession().connection();
		try {
			ps = con.prepareStatement(sql);
			int i= ps.executeUpdate();
//			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public void delete(WtMgrEncKey w){
		Connection con;
		PreparedStatement ps;
		String sql = "delete from Wt_Mgr_Enc_Key where MGR_TERM_ID = '"+w.getId().getMgrTermId()+"' and MGR_MER_ID = '"+w.getId().getMgrMerId().trim()+"' ";
		con = this.getSession().connection();
		try {
			ps = con.prepareStatement(sql);
			int i= ps.executeUpdate();
			
//			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public void delete(WtMgrMerInfo w){
		this.getHibernateTemplate().delete(w);
	}
	public void update(CsMgrCode c){
		this.getHibernateTemplate().update(c);
	}
	public void save(CsMgrCode c){
		this.getHibernateTemplate().save(c);
	}
	public void updateUser(CsCtrlUser u){
		this.getHibernateTemplate().update(u);
	}
	public void delete(CsSysTerminal c){
		this.getHibernateTemplate().delete(c);
	}
	public String[] getSamList() {
		List list = this
				.getSession()
				.createSQLQuery(
						"select * from samcard_info t where not exists (select * from wt_term_info q where term_simno = t.samc_cdno) order by samc_cdno").addEntity(SamcardInfo.class)
				.list();
		if (list.size() > 0) {
			String[] tmp = new String[list.size()];
			for (int i = 0; i < list.size(); i++) {
				tmp[i]=((SamcardInfo)list.get(i)).getSamcCdno().trim();
			}
			return tmp;
		}
		return null;
	}
	
	public List getMenuList(){
		return this.getSession().createSQLQuery("select * from Menu_Define where menu_state = '1'").addEntity(MenuDefine.class).list();
	}
	
	

	public String getCount(String tableName, String condition) {
		String hql = "select count(*) from " + tableName + " " + condition;
		System.out.println("hql1=" + hql);
		String tmp = this.getSession().createQuery(hql).list().get(0)
				.toString();
		System.out.println("count=" + tmp);
		return tmp;
	}

	public boolean delSAM(String sam, String phone) {
		try {
			this.getSession().createQuery(
					"DELETE FROM WtTermInfo where term_simno = '" + sam
							+ "' and term_code = '" + phone + "'").executeUpdate();
			return true;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}
	public String getSeqID(String tablename){
		String tmp =this.getSession().createSQLQuery("SELECT "+tablename+".NEXTVAL FROM dual").list().get(0).toString();
		return tmp;
	}
	
	public boolean delAct(String phone) {
		try {
			this.getSession().createQuery(
					"DELETE FROM PepCustinfo where  cust_callno = '" + phone + "'").executeUpdate();
			return true;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}
	
	public void updateSys(CsSysTerminal c){
		this.getHibernateTemplate().update(c);
	}
	public void updateWt (WtTermInfo c){
		this.getHibernateTemplate().update(c);
	}
	public void updateApp(CsAppTerminal c){
		this.getHibernateTemplate().update(c);
	}
	public void updateBak(CsBakTerminal c){
		this.getHibernateTemplate().update(c);
	}
	public void savaSys(CsSysTerminal c){
		this.getHibernateTemplate().setFlushMode(getHibernateTemplate().FLUSH_AUTO);
		this.getHibernateTemplate().save(c);
	}
	public void save(WtRcvAccountInfo w ){
		this.getHibernateTemplate().save(w);
	}
	public void update(WtRcvAccountInfo w ){
		this.getHibernateTemplate().update(w);
	}
	public void delete(WtRcvAccountInfo w ){
		this.getHibernateTemplate().delete(w);
	}
	public void save(CsRcvAccountInfo w){
		this.getHibernateTemplate().save(w);
	}
	public void delete(CsRcvAccountInfo w){
		this.getHibernateTemplate().delete(w);
	}
	public void saveApp(CsAppTerminal c){
		this.getHibernateTemplate().save(c);
	}
	public void delete(WtTermInfo w){
		this.getHibernateTemplate().delete(w);
	}
	public void delete(PepCustinfo p ){
		this.getHibernateTemplate().delete(p);
	}
	public boolean saveSamPhone(WtTermInfo wt) {
		try {
			this.getHibernateTemplate().save(wt);
			return true;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean saveAct(PepCustinfo pc) {
		try {
			if(pc.getCustDesc()==null||pc.equals("")){
				pc.setCustDesc("ok");
			}
			this.getHibernateTemplate().save(pc);
			return true;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}
	public void delCustInfo(PepCustinfo pc){
		this.getHibernateTemplate().delete(pc);
	}
	public void delWtTermInfo(WtTermInfo wt){
		this.getHibernateTemplate().delete(wt);
	}
	public boolean updateAct(PepCustinfo pc) {
		
		try {
			this.getHibernateTemplate().update(pc);
			return true;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean updatePhone(WtTermInfo w,String key){
		String key1 [] =key.split(",");
		try {
			this.getSession().createQuery("update WtTermInfo set term_simno = '"+w.getId().getTermSimno()+"',term_statu = '"+w.getTermStatu()+"',term_menuver = '"+w.getTermMenuver()+"',term_mumsgver = '"+w.getTermMenuver()+"',term_gnmsgver = '"+w.getTermMenuver()+"',term_addmsg = '"+w.getTermAddmsg()+"' where term_simno = '"+key1[0]+"' and term_code = '"+key1[1]+"'").executeUpdate();
			return true;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}
	public String executeSQL(String sql) {
		
		try {
			PreparedStatement ps;
			ps = this.getSession().connection().prepareStatement(sql);
			int i = ps.executeUpdate();
			if(i>0){
				return "ok";
			}else{
				return sql;
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return sql;
		} 
	}
	public String getBillID(){
		List l = this.getHibernateTemplate().find("from CsBillId");
		if(l.size()>0){
			CsBillId w = (CsBillId)l.get(0);
//			System.out.println(w.getExpressId()+" "+w.getExpressDate());
			if(w.getBillId()==999999){
				w.setBillId(Long.valueOf(1));
				w.setBillDate(Util.getCurrentDayID());
				this.update(w);
				return "000001";
			}else{
				long id =w.getBillId()+1;
				w.setBillId(id);
				this.update(w);
				return Util.addZero(id+"", 6);
			}
		}else{
			CsBillId w = new CsBillId();
			w.setBillDate(Util.getCurrentDayID());
			w.setBillId(Long.valueOf(1));
			w.setBillIndex(Long.valueOf(1));
			this.save(w);
			return "000001";
		}
	}
	public List getBakbySql(String sql){
		List list = new ArrayList();
//		PreparedStatement ps;
//		try {
//			ps = this.getSession().connection().prepareStatement(sql);
//			ResultSet rset = ps.executeQuery();
//			while (rset.next()) {
//				list.add(new CsBakTerminal(new CsBakTerminalId(rset.getString("termcode"), rset.getString("areano"), rset.getString("terminalno"),
//						rset.getString("extensionno"), rset.getString("termiannotype"), rset.getString("terminaltype"),
//						rset.getString("installaddress"), rset.getString("projectcode"), rset.getString("version"),
//						rset.getString("manufacturercode"), rset.getString("menugroupcode"), rset.getString("localtion"),
//						rset.getString("samno"), rset.getString("username"), rset.getString("usertype"),
//						rset.getString("enterprisetype"), rset.getString("artificialpersoncode"),
//						rset.getString("artificialperson"), rset.getString("employeenum"), rset.getString("licenceno"),
//						rset.getString("cardtype"), rset.getString("earnings"), rset.getString("userbanktype"),
//						rset.getString("linkman"), rset.getString("linkmanaddress"), rset.getString("linkmanphone"),
//						rset.getString("faxno"), rset.getString("homephone"), rset.getString("mobilephone"), rset.getString("email"),
//						rset.getString("post"), rset.getString("bangdingcommercode"), rset.getString("bangdingcommername"),
//						rset.getString("bangdingtermno"), rset.getString("bangdingaccounts"),
//						rset.getString("bangdingmobile"), rset.getString("bangdingflag"), rset.getString("iseveryterminal"),
//						rset.getString("gangwaycomm"), rset.getString("belongcomm"),rset.getString("surfacecode"),
//						rset.getString("rentcode"), rset.getString("state"), rset.getString("opstate"), rset.getString("appdt"),
//						rset.getString("updatedt"), rset.getString("deldt"), rset.getString("groupid"), rset.getString("appuser"),
//						rset.getString("updateuser"),rset.getString("orgcode"), rset.getString("remark"))));
//			}
//		} catch (DataAccessResourceFailureException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		} catch (HibernateException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		} catch (IllegalStateException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
		return list;
	}
	public boolean isExt(String key){
		System.out.println("key="+key);
		int i =this.getHibernateTemplate().find("from WtTermInfo where term_code = '"+key+"'").size();
		System.out.println("simSize="+i);
		if(i>0){
			return true;
		}else{
			return false;
		}
	}
	public boolean isOk(String key){
		System.out.println("key="+key);
		int i =this.getHibernateTemplate().find("from PepCustinfo where cust_callno = '"+key+"'").size();
		System.out.println("simSize="+i);
		if(i>0){
			return false;
		}else{
			return true;
		}
	}
}
